﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{

    public class AB55PRData : BaseFunction
    {
        
        public AB55PRData()
            : base()
        {

        }
        public AB55PRData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }
        public DataSet LoadData()
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tbLocal;
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT Distinct B.LSNAME1,A.ABPAUFKRS,A.ABPAUFLFD,A.ABPAUFPOS,A.ABPART,A.ABPVAR,A.ABPFARBE"
                +",A.ABPSTARTDAT,A.ABPENDDAT,A.ABPSOLLMENGE1/100 AS WEI,"
                +" '' AS REMARK,  C.ABADIAUFKRS||'-'||TO_NUMBER(C.ABADIAUFLFD)||'-'||TO_NUMBER(C.ABADIAUFPOS) AS DIORDER  "
                +" FROM ABPOS A,LIEFDAT B,ABAUFM C  "
                +" WHERE A.ABPMANDANT = B.LSMANDANT AND A.ABPLIEFNR = B.LSLIEFNR "
                +" AND A.ABPMANDANT = C.ABAMANDANT AND A.ABPAUFKRS = C.ABAAUFKRS  "
                +" AND A.ABPAUFLFD = C.ABAAUFLFD AND A.ABPAUFPOS = C.ABAAUFPOS  AND A.ABPAUFART='PR' "
                +" AND A.ABPERLKZ <>'J' AND C.ABAAPOS='0001' "
                ;

                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    tbLocal = setResult.Tables[0];
                    tbLocal.TableName = "ABPOS";
                    byte[] b;
                    string value;
                    string ColumnName;
                    foreach (DataRow dw in tbLocal.Rows)
                    {
                        ColumnName = "LSNAME1";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw[ColumnName] = value;
                    }
                }
                setReturn.Merge(setResult);

            }
            catch(Exception ex)
            {
                Tracer.Error(ex.StackTrace,ex);
            }
            return setReturn;
        }
        public DataSet GetWBBST(DataSet dsTarget, DataRow dwABPOS)
        {
            string TableName = "WBBST";
            try
            {

                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT WBBSTKNR,WBBSSTKLG/1000 AS STDMTR "
                    + " FROM WBBST WHERE WBBMANDANT = '00' "
                    + " AND WBBAUFKRS = :WBBAUFKRS AND WBBAUFLFD = :WBBAUFLFD AND WBBAUFPOS = :WBBAUFPOS ";
                orlReadCommand.Parameters.Add("WBBAUFKRS", OracleType.NVarChar).Value = dwABPOS["ABPAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("WBBAUFLFD", OracleType.NVarChar).Value = dwABPOS["ABPAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("WBBAUFPOS", OracleType.NVarChar).Value = dwABPOS["ABPAUFPOS"].ToString();
                
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;
                                        
                }
                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    if (dsTarget.Tables.Contains(TableName))
                    {
                        dsTarget.Tables[TableName].Clear();
                    }
                    dsTarget = ImportDataTable(dsTarget, dtTemp);
                }
                dsTarget.AcceptChanges();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }
       
        public DataSet GetABPOS(DataSet dsTarget, DataRow dwABPOS)
        {
            string TableName = "ABPOS";
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT Distinct trim(B.LSNAME1) AS FACTORY,A.ABPAUFKRS ,A.ABPAUFLFD,A.ABPAUFPOS,A.ABPART,A.ABPVAR,"
                    + " A.ABPFARBE,A.ABPSOLLMENGE1/100 AS WEI, A.ABPSTARTDAT,A.ABPENDDAT,'' AS REMARK, "
                    + " C.ABADIAUFKRS||'-'||TO_NUMBER(C.ABADIAUFLFD)||'-'||TO_NUMBER(C.ABADIAUFPOS) AS DIORDER, "
                    + " A.ABPAUFKRS||'-'||TO_NUMBER(A.ABPAUFLFD)||'-'||TO_NUMBER(A.ABPAUFPOS) AS PBORDER, "
                    + " D.WARSTDLG/1000 AS PLANMTR,D.WARWBRT/10 AS WIDTH,D.WARGRLFD,A.ABPANZSTK, "
                    + " D.WARFDDIK/100||'/'||D.WARFDDIS/100 AS EPI,D.WAEINZNR,E.HRNNAME,'' AS BEAMNO, "
                    + " '' AS BEAMMTR,'' AS BATCHNO,'' AS MACHINE "
                    + " FROM ABPOS A,LIEFDAT B,ABAUFM C,WASTAMM D,HRSTAMM E "
                    + " WHERE A.ABPMANDANT = B.LSMANDANT AND A.ABPLIEFNR = B.LSLIEFNR " 
                    + " AND A.ABPMANDANT = C.ABAMANDANT AND A.ABPAUFKRS = C.ABAAUFKRS "
                    + " AND A.ABPAUFLFD = C.ABAAUFLFD AND A.ABPAUFPOS = C.ABAAUFPOS "
                    + " AND A.ABPMANDANT = D.WAMANDANT AND A.ABPART = D.WAART "
                    + " AND TRIM(A.ABPVAR) = D.WAVAR AND A.ABPMANDANT = E.HRMANDANT "
                    + " AND A.ABPNEUBEN = E.HRBENID "
                    + " AND A.ABPAUFART='PR'  AND A.ABPERLKZ <>'J' AND C.ABAAPOS='0001'"
                    + " AND ABPAUFKRS = :ABPAUFKRS AND ABPAUFLFD=:ABPAUFLFD AND ABPAUFPOS = :ABPAUFPOS ";
                orlReadCommand.Parameters.Add("ABPAUFKRS", OracleType.NVarChar).Value = dwABPOS["ABPAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("ABPAUFLFD", OracleType.NVarChar).Value = dwABPOS["ABPAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("ABPAUFPOS", OracleType.NVarChar).Value = dwABPOS["ABPAUFPOS"].ToString();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;

                    byte[] b;
                    string value;
                    foreach (DataRow dw in dsResult.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["FACTORY"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["FACTORY"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["HRNNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["HRNNAME"] = value;
                    }
                }
                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    DataSet setUI = new DataSet();
                    setUI = ImportDataTable(setUI, dtTemp);
                    DataTable tableABPOS = setUI.Tables["ABPOS"];
                    foreach (DataRow dwTmp in tableABPOS.Rows)
                    {
                        setUI = GetBEAM(setUI, dwTmp);
                        setUI = GetMACHINE(setUI, dwTmp);
                        string order = dwTmp["ABPAUFKRS"].ToString() + dwTmp["ABPAUFLFD"].ToString() + dwTmp["ABPAUFPOS"].ToString();
                        dwTmp["REMARK"] = GetCommentsFromTXTIND("ABP", order);
                    }
                    
                    if (dsTarget.Tables.Contains(TableName))
                    {
                        dsTarget.Tables[TableName].Clear();
                    }
                    dsTarget = ImportDataTable(dsTarget, setUI.Tables["ABPOS"]);
                }
                dsTarget.AcceptChanges();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }
        public DataSet GetBEAM(DataSet dsTarget, DataRow dwABPOS)
        {

            string TableName = "BEAM";
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT distinct A.BBBAUMKRS||' '||to_number(A.BBBAUMLFD) AS BEAMNO,A.BBMTR/10 AS MTR, "
                    + " B.BMBEM FROM BABEW A,BABST B WHERE A.BBMANDANT = B.BMMANDANT  "
                    + " AND A.BBBAUMKRS = B.BMBAUMKRS AND A.BBBAUMLFD = B.BMBAUMLFD "
                    + " AND A.BBBS = '61' "
                    + " AND A.BBAUFKRS = :BBAUFKRS AND A.BBAUFLFD = :BBAUFLFD AND A.BBAUFPOS =:BBAUFPOS "
                    ;
                orlReadCommand.Parameters.Add("BBAUFKRS", OracleType.NVarChar).Value = dwABPOS["ABPAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("BBAUFLFD", OracleType.NVarChar).Value = dwABPOS["ABPAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("BBAUFPOS", OracleType.NVarChar).Value = dwABPOS["ABPAUFPOS"].ToString();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = "BEAM";
                    string BEAMNO = "";
                    double BEAMMTR = 0;
                    string BATCHNO = "";
                    foreach (DataRow dwTemp in dsResult.Tables["BEAM"].Rows)
                    {
                        BEAMNO += dwTemp["BEAMNO"].ToString() + ";";
                        BEAMMTR += System.Convert.ToDouble(dwTemp["MTR"].ToString());
                        BATCHNO += dwTemp["BMBEM"].ToString() + ";";
                    }
                    dwABPOS["BEAMNO"] = BEAMNO;
                    dwABPOS["BEAMMTR"] = BEAMMTR;
                    dwABPOS["BATCHNO"] = BATCHNO;
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;

        }
        public DataSet GetMACHINE(DataSet dsTarget, DataRow dwABPOS)
        {
            string TableName = "MACHINE";
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT ABFMNRKRS||'-'||TO_NUMBER(ABFMLFDNR)||'-'||TO_NUMBER(ABFMPOS) AS MACHINE "
                    + " FROM ABFOLGE WHERE ABFMANUELKZ ='N' "
                    + " AND ABFAUFKRS = :ABFAUFKRS  AND ABFAUFLFD = :ABFAUFLFD AND ABFAUFPOS = :ABFAUFPOS "
                    ;
                orlReadCommand.Parameters.Add("ABFAUFKRS", OracleType.NVarChar).Value = dwABPOS["ABPAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("ABFAUFLFD", OracleType.NVarChar).Value = dwABPOS["ABPAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("ABFAUFPOS", OracleType.NVarChar).Value = dwABPOS["ABPAUFPOS"].ToString();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = "MACHINE";
                    string MACHINE = "";
                    foreach (DataRow dwTemp in dsResult.Tables["MACHINE"].Rows)
                    {
                        MACHINE += dwTemp["MACHINE"].ToString() ;
                    }
                    dwABPOS["MACHINE"] = MACHINE;
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;

        }
        public DataSet BuildAB55PRData(DataSet dsTarget, DataRow dwABPOS)
        {
            try
            {
                dsTarget = GetABPOS(dsTarget, dwABPOS);
                dsTarget = GetWBBST(dsTarget, dwABPOS);

            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }
    }
}